package com.example.appframework.mysql;

import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class DBConnection {
    //add增
    public static void add (String username, String password, String phone_number, int is_bound){
        /*Log.i("temp", username);
        Log.i("temp", password);
        Log.i("temp", phone_number);
        Log.i("temp", String.valueOf(is_bound));*/
        Log.i("show", username);
        Log.i("show", password);
        Log.i("show", phone_number);
        Log.i("show", String.valueOf(is_bound));
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "INSERT INTO information (username, password, phone_number, is_bound) VALUES ('" + username + "','" + password + "','" + phone_number + "','" + is_bound + "');" ;
            Log.i("show", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //update修改密码
    public static void update (String username, String newpassword){
        //Log.i("temp", username);
        //Log.i("temp", newpassword);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "UPDATE information SET password='" + newpassword + "' WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //query查
    public static Boolean query (String username, String password){
        Boolean flag = false;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT password FROM information WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String realpassword = rs.getString("password");
                //Log.i("temp", realpassword);
                if(realpassword.equals(password)){
                    flag = true;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        Log.i("temp", String.valueOf(flag));
        return flag;
    }

    //delete删
    public static void delete (String username, String password){
        //Log.i("temp", username);
        //Log.i("temp", password);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "DELETE FROM information WHERE username='" + username + "' AND password='" + password + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //检查用户名是否存在
    public static Boolean check_usename (String username){
        //Log.i("temp", username);
        Boolean flag = false;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT * FROM information WHERE username='" + username + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if(!rs.first()){
                flag = false;
            }
            else{
                flag = true;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        //Log.i("temp", String.valueOf(flag));
        return flag;
    }

    //检查手机是否被绑定
    public static Boolean check_phone_number (String phone_number){
        //Log.i("temp", username);
        Boolean flag = false;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT * FROM information WHERE phone_number='" + phone_number + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            if(!rs.first()){
                flag = false;
            }
            else{
                flag = true;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        //Log.i("temp", String.valueOf(flag));
        return flag;
    }

    //update忘记密码
    public static void update_retrieve (String phone_number, String newpassword){
        //Log.i("temp", username);
        //Log.i("temp", newpassword);
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "UPDATE information SET password='" + newpassword + "' WHERE phone_number='" + phone_number + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ps.execute(sql);
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    //query查
    public static String get_username (String phone_number){
        String username = null;
        String url = "jdbc:mysql://bj-cynosdbmysql-grp-9wzbqaew.sql.tencentcdb.com:20046/users?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        Connection connection=null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        }
        catch (Exception e){
            e.printStackTrace();
        }
        try {
            connection = DriverManager.getConnection(url, "test", "q1w2e3r4_");
            String sql = "SELECT username FROM information WHERE phone_number='" + phone_number + "';";
            //Log.i("temp", sql);
            PreparedStatement ps=connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                username = rs.getString("username");
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if(connection!=null){
                try {
                    connection.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
        Log.i("show", username);
        return username;
    }
}
